package dbLayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import modelLayer.Class;
import modelLayer.Teacher;

/**
 * This class is a database layer class of the MSAS system. It manipulates database
 * information of class objects. This class implements its interface, and have therefore
 * no direct connection to the control layer.
 * 
 * @author Danny M�lgaard, Anna Kelemen, Jakob Korsgaard
 * 		   
 * @version 1.3.1
 * 					1.3.1: 		Added more extensive documentation. Verified that
 * 								information contained in the Design Class Diagram,
 * 								is in accordance with the information contained in this
 * 								class.
 */
public class DBClass implements DBClassIF {

	private Connection dbConnection;

	public DBClass() {

		this.dbConnection = DBConnection.getInstance().getDBcon();

	}

	@Override
	public int insert(String name, String grade, String yearOf, Teacher classTeacher) throws SQLException {

		int rc = -1;
		String query = "INSERT INTO Class (name, grade, year_of, teacher)"
				+ "values(?, ?, ?, ?)";

		try {
			PreparedStatement ps = this.dbConnection.prepareStatement(query);
			ps.setString(1, name);
			ps.setString(2, grade);
			ps.setString(3, yearOf);
			ps.setInt(4, classTeacher.getId());
			
			rc = ps.executeUpdate();
			ps.close();

		} 
		catch (SQLException e) {
			System.out.println("Fejl ved insertion" + e);
			throw new SQLException();
		}

		return rc;
	}

	@Override
	public Class find(String name, boolean retriveAssociation) {

		String wClause = " name = '" + name + "'";
		return singleWhere(wClause, retriveAssociation);
		
	}

	@Override
	public Class findById(int classId, boolean retriveAssociation) {

		String wClause = " id = '" + classId + "'";
		return singleWhere(wClause, retriveAssociation);
		
	}

	@Override
	public ArrayList<Class> getAll(boolean retrieveAssociation) {
		String wClause = "";
		return miscWhere(wClause, retrieveAssociation);
		
	}

	@Override
	public int update(Class cl) throws SQLException {

		int rc = -1;

		String query = "UPDATE Class SET name = ?, grade = ?, year_of = ?, teacher = ? WHERE id = ?";

		try {

			PreparedStatement ps = this.dbConnection.prepareStatement(query);
			ps.setString(1, cl.getClassName());
			ps.setString(2, cl.getGrade());
			ps.setString(3, cl.getYearOf());
			ps.setInt(4, cl.getClassTeacher().getId());
			ps.setInt(5, cl.getId());
			rc = ps.executeUpdate();
			ps.close();
		}
		catch (SQLException ex) {
			System.out.println("SQL Error: " + ex);
			throw new SQLException();
		}
		return rc;
		
	}

	@Override
	public int remove(int id) throws SQLException {

		int rc = -1;

		PreparedStatement ps;
		String query = "DELETE FROM Class WHERE id = ?";

		try {
			ps = this.dbConnection.prepareStatement(query);
			ps.setInt(1, id);
			rc = ps.executeUpdate();
			ps.close();
		}  
		catch (SQLException e) {
			System.out.println(e);
			throw new SQLException(e);
		}
		return rc;

	}





	/**
	 * Builds a query with a where clause (condition), and returns a single object
	 * to the caller. This method is used when only wanting one object built from 
	 * the database. This method takes the following parameters:
	 *  
	 * @param 	wClause				String		the search condition, and where clause of the SQL query.
	 * @param 	retriveAssociation	boolean		states if we're to retrieve associations in the object aswell.
	 * @return	cl					Class		a built Class object, based on query results from the database.
	 */
	private Class singleWhere(String wClause, boolean retrieveAssociation) {

		ResultSet rs;
		Class cl = new Class();
		String query = buildQuery(wClause);
		try {
			Statement stmt = this.dbConnection.createStatement();
			stmt.setQueryTimeout(5);
			rs = stmt.executeQuery(query);
			if (rs.next()) {
				cl = buildObject(rs);
				stmt.close();
				if (retrieveAssociation) {
					int tId = cl.getClassTeacher().getId();
					Teacher tObj = new DBTeacher().findById(tId);
					cl.setClassTeacher(tObj);
				}
			} 
			else { 
				cl = null; 
			}
		} catch (Exception e) {
			System.out.println("DEBUG MESSAGE [CLASS]: singleWhere(): " + e);
		}
		return cl;
		
	}

	/**
	 * Builds a query with a where clause (condition), and returns a collection of objects
	 * to the caller. This method is used when only wanting one or more objects built from 
	 * the database. This method takes the following parameters:
	 *  
	 * @param 	wClause				String				the search condition, and where clause of the SQL query.
	 * @param 	retriveAssociation	boolean				states if we're to retrieve associations in the object aswell.
	 * @return	list				ArrayList<Class>	a list of built Class objects, based on query results from the 
	 * 													database.
	 */
	private ArrayList<Class> miscWhere(String wClause, boolean retrieveAssociation) {

		ResultSet rs;
		ArrayList<Class> list = new ArrayList<Class>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = this.dbConnection.createStatement();
			stmt.setQueryTimeout(5);
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				Class cl = new Class();
				cl = buildObject(rs);
				list.add(cl);
			}
			stmt.close();
			if (retrieveAssociation) {
				for (Class clObj : list) {
					int tId = clObj.getClassTeacher().getId();
					Teacher tObj = new DBTeacher().findById(tId);
					clObj.setClassTeacher(tObj);
				}
			}
		} catch (Exception e) {
			System.out.println("DEBUG MESSAGE [CLASS]: miscWhere(): " + e);
		}
		return list;
		
	}
	
	/**
	 * Builds a query with a where clause (condition), and returns a string to the caller.
	 * This method takes the following parameters:
	 *  
	 * @param 	wClause				String		the search condition, and where clause of the SQL query.
	 * @return	query				String		an SQL query as a String.
	 */
	private String buildQuery(String wClause) {

		String query = "SELECT id, name, grade, year_of, teacher FROM Class";
		if (wClause.length() > 0) {
			query = query + " WHERE " + wClause;
		}
		
		return query;

	}
	
	/**
	 * builds an object of type Class based on the resultset of the SQL query. Returns this
	 * object to the caller.
	 * 
	 * @param 	rs		ResultSet		the resultset from the SQL query.
	 * @return	cl		Class			the build Class object.
	 */
	private Class buildObject(ResultSet rs) {

		Class cl = new Class();
		try {
			cl.setId(rs.getInt("id"));
			cl.setClassName(rs.getString("name"));
			cl.setYearOf(rs.getString("year_of"));
			cl.setGrade(rs.getString("grade"));
			cl.setClassTeacher(new Teacher(rs.getInt("teacher")));
		} catch (Exception e) {
			System.out.println("Fejl under opbygning af object");
			e.printStackTrace();
		}

		return cl;
		
	}

}
